package com.rlyy.rsj.job.statistic;

import com.alibaba.fastjson.JSON;
import com.rlyy.aibase.db.Dbink;
import com.rlyy.aibase.utils.base.TableNode;
import com.rlyy.rsj.db.PostgresqlConn;
import com.rlyy.rsj.utils.QueryRunnerFactory;
import com.rlyy.rsj.utils.UniqueID;
import common.exec.RsjIllegalArgueException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 定时统计产品使用量
 *
 * @author yuanqinglong
 * @since 2021/3/11 13:49
 */
public interface IStatisticUsageJob {

    Logger logger = LoggerFactory.getLogger(IStatisticUsageJob.class);

    /**
     * 执行统计
     *
     * @param statisticsDate 统计日期
     * @param environment    环境（01：测试环境、02：生产环境）
     */
    default void performStatistics(LocalDate statisticsDate, String environment) {
        this.performStatistics(statisticsDate, null, environment);
    }


    /**
     * 执行统计
     *
     * @param statisticsDate 统计日期
     * @param orgId          统计机构id
     * @param environment    环境（01：测试环境、02：生产环境）
     */
    @Transactional(rollbackFor = Exception.class)
    default void performStatistics(LocalDate statisticsDate, String orgId, String environment) {
        Connection connection = PostgresqlConn.getConnection();
        try {
            // 1.查询统计数据
            List<StatisticColumnEntity> statisticColumnEntities = this.performStatisticalQueries(statisticsDate, connection, orgId);
            // 2.添加统计数据到统计表
            int addSuccess = this.addToTheStatisticalTable(statisticColumnEntities, connection, statisticsDate, environment);
            logger.info("执行产品使用情况成功：{}", addSuccess);
        } catch (Exception e) {
            logger.error("执行产品使用情况统计异常：", e);
            throw new RsjIllegalArgueException("执行产品使用情况统计异常");
        } finally {
            Dbink.close(connection);
        }
    }


    /**
     * 执行统计查询
     *
     * @param statisticsDate 统计日期
     * @param connection     数据库连接
     * @param orgId          机构id
     * @return 统计数据
     */
    default List<StatisticColumnEntity> performStatisticalQueries(LocalDate statisticsDate, Connection connection, String orgId) {
        String sqlStatement = this.statisticalSqlStatement(statisticsDate, orgId);
        TableNode tableNode = QueryRunnerFactory.getQueryRunner().queryLinkTableNode(connection, sqlStatement);
        if (tableNode.length() > 0) {
            return JSON.parseArray(tableNode.toJson(), StatisticColumnEntity.class);
        }
        return Collections.emptyList();
    }


    /**
     * 执行将统计数据添加到统计表
     *
     * @param statisticColumnEntities 统计数据
     * @param connection              数据库连接
     * @param statisticsDate          统计日期
     * @param environment             环境（01：测试环境、02：生产环境）
     * @return 执行成功数
     * @throws SQLException 异常
     */
    default int addToTheStatisticalTable(List<StatisticColumnEntity> statisticColumnEntities, Connection connection, LocalDate statisticsDate, String environment) throws SQLException {
        if (!CollectionUtils.isEmpty(statisticColumnEntities)) {
            // 添加到统计表INSERT SQL语句
            String addToTableSql = this.addToStatisticalTableSql(statisticColumnEntities, statisticsDate, environment);
            int batch = QueryRunnerFactory.getQueryRunner().update(connection, addToTableSql);
            return batch;
        }
        return statisticColumnEntities.size();
    }


    /**
     * 统计产品使用量SQL语句
     *
     * @param statisticsDate 统计日期
     * @param orgId          统计机构id（如果传递机构id 则是手动触发此机构的统计 、未传递机构id统计指定统计日期的所有机构的使用情况）
     * @return SQL
     */
    String statisticalSqlStatement(LocalDate statisticsDate, String orgId);


    /**
     * 将统计数据添加到统计表SQL
     *
     * @param statisticColumnEntities 统计数据
     * @param statisticsDate        统计日期
     * @param environment           环境（01：测试环境、02：生产环境）
     * @return 添加SQL
     */
    default String addToStatisticalTableSql(List<StatisticColumnEntity> statisticColumnEntities, LocalDate statisticsDate, String environment) {
        String insert = " INSERT INTO sys.sys_stat (stat_id, stat_org_id, stat_orgp_id, stat_prod_uid, stat_prod_name, stat_env, stat_date, " +
                " stat_req_num, stat_findout_num, stat_null_num, stat_fail_num, stat_charge_num, stat_up_useamount) VALUES ";
        String upset = " ON CONFLICT(stat_date,stat_org_id,stat_orgp_id,stat_env,stat_prod_uid) " +
                " DO UPDATE SET stat_req_num = EXCLUDED.stat_req_num," +
                " stat_findout_num = EXCLUDED.stat_findout_num, " +
                " stat_null_num = EXCLUDED.stat_null_num, " +
                " stat_fail_num = EXCLUDED.stat_fail_num, " +
                " stat_charge_num = EXCLUDED.stat_charge_num, " +
                " stat_up_useamount = EXCLUDED.stat_up_useamount, " +
                " stat_prod_name = EXCLUDED.stat_prod_name, " +
                " stat_prod_uid = EXCLUDED.stat_prod_uid ";
        String values = statisticColumnEntities.stream().map(statisticColumnEntity -> "(" +
                "'" + UniqueID.generateId() + "', " +
                "'" + statisticColumnEntity.getStatOrgId() + "', " +
                "'" + statisticColumnEntity.getStatOrgpId() + "', " +
                "'" + statisticColumnEntity.getStatProdUid() + "', " +
                "'" + statisticColumnEntity.getStatProdName() + "', " +
                "'" + environment + "', " +
                "'" + statisticsDate + "', " +
                "" + statisticColumnEntity.getStatReqNum() + ", " +
                "" + statisticColumnEntity.getStatFindoutNum() + ", " +
                "" + statisticColumnEntity.getStatNullNum() + ", " +
                "" + statisticColumnEntity.getStatFailNum() + ", " +
                "" + statisticColumnEntity.getStatChargeNum() + ", " +
                "" + statisticColumnEntity.getStatUpUseamount() + " " +
                ")").collect(Collectors.joining(","));
        return insert + values + upset;
    }


}
